package top.yudoge.dogeeditorserverj.db;

import top.yudoge.dogeeditorserverj.db.exceptions.DatabaseInfoFetchException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public abstract class AbstractSQLDataSourceConnector implements SQLDataSourceConnector {

    protected abstract Connection getConnection() throws SQLException;

    @Override
    public List<Database> getDatabases() {
        try {
            Connection connection = getConnection();
            List<Database> databases = new ArrayList<>();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet catalogs = metaData.getCatalogs();
            while (catalogs.next()) {
                String catalog = catalogs.getString(1);
                Database db = Database.builder().name(catalog).build();
                databases.add(db);
            }
            return databases;
        } catch (SQLException e) {
            throw new DatabaseInfoFetchException("Cannot fetch database info", e);
        }
    }

    public List<Schema> getSchemas(String dbName) {
        try {
            DatabaseMetaData metaData = getConnection().getMetaData();
            List<Schema> schemaList = new ArrayList<>();
            ResultSet schemas = metaData.getSchemas(dbName, "%");
            while (schemas.next()) {
                String schemaName = schemas.getString("TABLE_CAT");
                Schema schema = Schema.builder().name(schemaName).build();
                schemaList.add(schema);
            }
            return schemaList;
        } catch (SQLException e) {
            throw new DatabaseInfoFetchException("Cannot fetch schema info", e);
        }
    }

    public List<Table> getTables(String dbName, String schema) {
        try {
            DatabaseMetaData metaData = getConnection().getMetaData();
            List<Table> tableList = new ArrayList<>();
            ResultSet tables = metaData.getTables(dbName, schema, "%", new String[]{"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                String tableComment = tables.getString("REMARKS");

                Table table = new Table();
                table.setTableName(tableName);
                table.setComment(tableComment);

                tableList.add(table);
            }
            return tableList;
        } catch (SQLException e) {
            throw new DatabaseInfoFetchException("Cannot fetch table info", e);
        }

    }

    public List<Index> getIndices(String dbName, String schema, String tableName) {
        try {
            DatabaseMetaData metaData = getConnection().getMetaData();
            ResultSet indexInfo = metaData.getIndexInfo(dbName, schema, tableName, false, false);
            List<Index> indexList = new ArrayList<>();
            while (indexInfo.next()) {
                String s_indexName = indexInfo.getString("INDEX_NAME");
                boolean s_nonUniq = indexInfo.getBoolean("NON_UNIQUE");
                String s_columnName = indexInfo.getString("COLUMN_NAME");
                Index index = new Index();

                index.setName(s_indexName);
                index.setColumns(s_columnName);
                index.setUnique(!s_nonUniq);

                indexList.add(index);
            }
            return indexList;
        } catch (SQLException e) {
            throw new DatabaseInfoFetchException("Cannot fetch index info", e);
        }
    }

    public List<Column> getColumns(String catalog, String schema, String tableName) {
        try {
            DatabaseMetaData metaData = getConnection().getMetaData();
            List<Column> columnsList = new ArrayList<>();
            ResultSet columns = metaData.getColumns(catalog, schema, tableName, "%");

            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String typeName = columns.getString("TYPE_NAME");
                String columnDef = columns.getString("REMARKS");
                Column column = new Column();
                column.setName(columnName);
                column.setType(typeName);
                column.setComment(columnDef);

                columnsList.add(column);
            }
            return columnsList;
        } catch (SQLException e) {
            throw new DatabaseInfoFetchException("Cannot fetch table info", e);
        }
    }

    @Override
    public void onDestroy() {
        try {
            getConnection().close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
